﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.IO;
using System.Drawing;
using LetiLib.Device;
using LetiLib.Data;
using System.Diagnostics;
using LetiLib.Utils;

namespace LetiLib.Database
{
    // Singleton data base engine
    public sealed class XmlDatabase
    {
        #region | Singleton construction |
        private static readonly Lazy<XmlDatabase> lazy =
            new Lazy<XmlDatabase>(() => new XmlDatabase());

        public static XmlDatabase Instance { get { return lazy.Value; } }
        #endregion

        #region | Constant |
        /*
         * Constant define for database
         * xml data base struct version 1.0 created 28/3/2013 by tungpt
         * <?xml version="1.0" encoding="UTF-8"?>
            <LedCenter >
              <Devices>
                <Device ID="dev1" Name="TCPDevice1" IMEI="111" Password="12345" Type="TCP">
                  <DataTCP>
                    <Address>192.168.175.175</address>
                    <Port>6868</port>
                  </DataTCP>
                  <Latitude>21.036602</Latitude>
                  <Longitude>105.849838</Longitude>
                </Device>
                <Device ID="dev2" Name="SMSDevice1" IMEI="112" Password="12345" Type="SMS">
                  <DataSMS>
                    <PhoneNumber>0919992959</PhoneNumber>
                    <Mode>PDU</Mode>
                  </DataSMS>
                  <Latitude>22.036602</Latitude>
                  <Longitude>105.849838</Longitude>
                </Device>
              </Devices>
              <Playlists>
                <Playlist ID="1" Name="Playlist1" Delay="5" StartDate="2/9/2013 0:0:0" EndDate="4/9/2013 0:0:0">
                  <Program Index="1" Type="IMAGE">
                    <Image Width="128" Height="64" Bpp="2">
                      <![CDATA[AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADw//8P//8P8P///wAAAAAAAAAAAAAAAAAAAAAAAAAAAPD//w///z/w////AAAAAAAAAAAAAAAAAAAAAAAAAAAA8P//D/////D///8AAAAAAAAAAAAAAAAAAAAAAAAAAADwDwAA/wD/A8A/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPAPAAD/APwDwD8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8A8AAP8A/APAPwAAAAAAAAAAAAAAAAAAAAAAAAAAAADwDwAA/wD8A8A/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPD//wP/APwDwD8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8P//A/8A/wDAPwAAAAAAAAAAAAAAAAAAAAAAAAAAAADw//8D////AMA/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPAPAAD//z8AwD8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8A8AAP//AwDAPwAAAAAAAAAAAAAAAAAAAAAAAAAAAADwDwAA/wAAAMA/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPAPAAD/AAAAwD8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8A8AAP8AAADAPwAAAAAAAAAAAAAAAAAAAAAAAAAAAADwDwAA/wAAAMA/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPAPAAD/AAAAwD8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8A8AAP8AAADAPwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAP//////////////AwAAAAAAAAAAAAAAAAAAAAAAAAAA//////////////8DAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=]]>
                    </Image>
                  </Program>
                  <Program Index="2" Type="TEXT">
                    <Text Font="Arial" Size="10" Color="YELLOW" PositionX="0" PositionY="0">
                      FPT
                      TECHNOLOGY
                      SOLUTION
                    </Text>
                  </Program>
                  <Program Index="3" Type="TEXT">
                    <Text Font="Arial" Size="10" Color="GREEN" PositionX="0" PositionY="0">
                      FPT
                      TECHNOLOGY
                      SOLUTION
                    </Text>
                  </Program>
                </Playlist>
              </Playlists>
            </LedCenter>
         * */
        // Database name
        private string _DATABASE_NAME = @"";
        public string DATABASE_NAME
        {
            get { return _DATABASE_NAME; }
            set { 
                _DATABASE_NAME = value;
                // Create database if not exists
                if (!File.Exists(_DATABASE_NAME))
                {
                    _DATABASE_NAME = "";
                    return;
                }

                // Load database from file.
                rootElement = XElement.Load(DATABASE_NAME);
            }
        }
        // Device table name
        private const string DEVICE_TABLE = @"Devices";
        // Device record name
        private const string DEVICE_RECORD = @"Device";
        // Device's attribute ID
        private const string DEVICE_ATTRIBUTE_ID = @"ID";
        // Device's attribute Name
        private const string DEVICE_ATTRIBUTE_NAME = @"Name";
        // Device's attribute IMEI
        private const string DEVICE_ATTRIBUTE_IMEI = @"IMEI";
        // Device's attribute Password
        private const string DEVICE_ATTRIBUTE_PASSWORD = @"Password";
        // Device's attribute Type
        private const string DEVICE_ATTRIBUTE_TYPE = @"Type";
        // TCPDevice data record
        private const string DEVICE_TCP_RECORD = @"DataTCP";
        // TCPDevice data record
        private const string DEVICE_TCP_ADDRESS_RECORD = @"Address";
        // TCPDevice data record
        private const string DEVICE_TCP_PORT_RECORD = @"Port";
        // SMSDevice data record
        private const string DEVICE_SMS_RECORD = @"DataSMS";
        // SMSDevice data record
        private const string DEVICE_SMS_PHONENUMBER_RECORD = @"PhoneNumber";
        // SMSDevice data record
        private const string DEVICE_SMS_MODE_RECORD = @"Mode";
        // Device latitude record
        private const string DEVICE_LATITUDE_RECORD = @"Latitude";
        // Device longitude record
        private const string DEVICE_LONGITUDE_RECORD = @"Longitude";

        // Playlist table name
        private const string PLAYLIST_TABLE = @"Playlists";
        // Playlist record name
        private const string PLAYLIST_RECORD = @"Playlist";
        // Playlist's attribute ID
        private const string PLAYLIST_ATTRIBUTE_ID = @"ID";
        // Playlist's attribute Name
        private const string PLAYLIST_ATTRIBUTE_NAME = @"Name";
        // Playlist's attribute IMEI
        private const string PLAYLIST_ATTRIBUTE_DELAY = @"Delay";
        // Playlist's attribute Password
        private const string PLAYLIST_ATTRIBUTE_STARTDATE = @"StartDate";
        // Playlist's attribute Type
        private const string PLAYLIST_ATTRIBUTE_ENDDATE = @"StartDate";

        // Program record name
        private const string PROGRAM_RECORD = @"Program";
        // Program's attribute Index
        private const string PROGRAM_ATTRIBUTE_INDEX = @"Index";
        // Program's attribute Type
        private const string PROGRAM_ATTRIBUTE_TYPE = @"Type";

        // Text record name
        private const string PROGRAM_TEXT_RECORD = @"Text";
        // Text's attribute Index
        private const string PROGRAM_TEXT_ATTRIBUTE_FONT = @"Font";
        // Text's attribute Type
        private const string PROGRAM_TEXT_ATTRIBUTE_SIZE = @"Size";
        // Text's attribute Index
        private const string PROGRAM_TEXT_ATTRIBUTE_COLOR = @"Color";
        // Text's attribute Width
        private const string PROGRAM_TEXT_ATTRIBUTE_WIDTH = @"Width";
        // Text's attribute Index
        private const string PROGRAM_TEXT_ATTRIBUTE_HEIGHT = @"Height";
        // Text's attribute Type
        private const string PROGRAM_TEXT_ATTRIBUTE_POSX = @"PositionX";
        // Text's attribute Type
        private const string PROGRAM_TEXT_ATTRIBUTE_POSY = @"PositionY";

        // Image record name
        private const string PROGRAM_IMAGE_RECORD = @"Image";
        // Image's attribute Index
        private const string PROGRAM_IMAGE_ATTRIBUTE_WIDTH = @"Width";
        // Image's attribute Type
        private const string PROGRAM_IMAGE_ATTRIBUTE_HEIGHT = @"Height";
        // Image's attribute Index
        private const string PROGRAM_IMAGE_ATTRIBUTE_BPP = @"Bpp";

        // Swap table name
        private const string PAGE_TABLE = @"PAGEDATA";
        // Swap record name
        private const string PAGE_RECORD = @"PAGE";
        // Swap's attribute ID
        private const string PAGE_ATTRIBUTE_ID = @"ID";
        // Swap's attribute ItemCount
        private const string PAGE_ATTRIBUTE_ITEM_COUNT = @"ItemCount";
        // Swap's attribute EventType
        private const string PAGE_ATTRIBUTE_EVENT_TYPE = @"EventType";

        // Popup record name
        private const string POPUP_RECORD = @"POPUP";
        // Popup's attribute ID
        private const string POPUP_ATTRIBUTE_ID = @"ID";
        // Popup's attribute X
        private const string POPUP_ATTRIBUTE_X = @"X";
        // Popup's attribute Y
        private const string POPUP_ATTRIBUTE_Y = @"Y";
        // Popup's attribute Z
        private const string POPUP_ATTRIBUTE_Z = @"Z";
        // Popup's attribute Width
        private const string POPUP_ATTRIBUTE_WIDTH = @"Width";
        // Popup's attribute Height
        private const string POPUP_ATTRIBUTE_HEIGHT = @"Height";
        // Popup's attribute Data type
        private const string POPUP_ATTRIBUTE_DATA_TYPE = @"Type";
        // Popup's attribute Item count
        private const string POPUP_ATTRIBUTE_ITEM_COUNT = @"ItemCount";
        // Popup's attribute Active item
        private const string POPUP_ATTRIBUTE_ACTIVE_ITEM = @"ActiveItem";
        // Popup's attribute Start time
        private const string POPUP_ATTRIBUTE_START = @"Start";
        // Popup's attribute End time
        private const string POPUP_ATTRIBUTE_END = @"End";

        
        // Image record name
        private const string IMAGE_RECORD = @"IMAGE";
        // Image's attribute PlayOrder
        private const string IMAGE_ATTRIBUTE_PLAY_ORDER = @"PlayOrder";
        // Image's attribute ImageCode
        private const string IMAGE_ATTRIBUTE_IMAGE_CODE = @"ImageCode";
        // Image's attribute Delay
        private const string IMAGE_ATTRIBUTE_DELAY = @"Delay";
        // Image's attribute Effect
        private const string IMAGE_ATTRIBUTE_EFFECT = @"Effect";

        // Table record name
        private const string TABLE_RECORD = @"TABLE";
        // TABLE's attribute Row
        private const string TABLE_ATTRIBUTE_ROW = @"Row";
        // Image's attribute Column
        private const string TABLE_ATTRIBUTE_COLUMN = @"Column";
        // Image's attribute SplitSymbol
        private const string TABLE_ATTRIBUTE_SYMBOL = @"SplitSymbol";

        // Image record name
        private const string IMAGE_DATA_TABLE = @"Images";
        private const string IMAGE_DATA_RECORD = @"Img";
        // Image's attribute Delay
        private const string IMAGE_DATA_ATTRIBUTE_ID = @"ID";
        // Image's attribute Effect
        private const string IMAGE_DATA_ATTRIBUTE_PATH = @"src";
        #endregion

        #region | Constructor |
        public XmlDatabase()
        {
            DATABASE_NAME = @"E:\Projects\LEDMATRIX\SVN\trunk\WIP\tungpt\LedCenter\LedCenter\bin\Debug\DB\LedCenter.xml";
        }

        private XElement rootElement;
        #endregion

        #region | Database access functions |
        /// <summary>
        /// Get list device from database
        /// </summary>
        /// <returns>List devices</returns>
        public List<BaseDevice> getDevices()
        {
            // Create list device
            List<BaseDevice> listDev = new List<BaseDevice>();

            // Query device node from data base
            // Select * from Devices
            IEnumerable<XElement> devices = from el in rootElement.Descendants(DEVICE_RECORD)
                                            select el;

            string id,name,imei,pass,type;
            // Create device object base on each device node
            foreach (XElement devEl in devices)
            {
                // device's id
                id = devEl.Attribute(DEVICE_ATTRIBUTE_ID).Value;
                // device's name
                name = devEl.Attribute(DEVICE_ATTRIBUTE_NAME).Value;
                // device's imei
                imei = devEl.Attribute(DEVICE_ATTRIBUTE_IMEI).Value;
                // device's type
                type = devEl.Attribute(DEVICE_ATTRIBUTE_TYPE).Value;
                // device's password
                pass = devEl.Attribute(DEVICE_ATTRIBUTE_PASSWORD).Value;
                // convert type string to DEVICE_TYPE enum
                DEVICE_TYPE devType = (DEVICE_TYPE)Enum.Parse(typeof(DEVICE_TYPE), type);

                // Check device type
                if (devType == DEVICE_TYPE.TCP) // If device type is TCP device, then create TCPDevice object and add to list
                {
                    // Select ip,port from TCP record
                    var tcp = (from w in devEl.Elements(DEVICE_TCP_RECORD)
                              select new {
                                  ip = w.Element(DEVICE_TCP_ADDRESS_RECORD).Value,
                                  port = w.Element(DEVICE_TCP_PORT_RECORD).Value
                              }).FirstOrDefault();
                    // create TCPDevice object and add to list
                    TCPDevice tcpDev = new TCPDevice(name, imei, pass, tcp.ip, int.Parse(tcp.port));
                    tcpDev.pages = getPagesDataInNode(devEl);
                    listDev.Add(tcpDev);
                }
                else if (devType == DEVICE_TYPE.SMS)// If device type is SMS device, then create SMSDevice object and add to list
                {
                    // Select phone number, mode from SMS record
                    var sms = (from w in devEl.Elements(DEVICE_SMS_RECORD)
                               select new
                               {
                                   phone = w.Element(DEVICE_SMS_PHONENUMBER_RECORD).Value,
                                   mode = (SMS_MODE)Enum.Parse(typeof(SMS_MODE), w.Element(DEVICE_SMS_MODE_RECORD).Value)
                               }).FirstOrDefault();
                    // create SMSDevice object and add to list
                    SMSDevice smsDev = new SMSDevice(name, imei, pass, sms.phone, sms.mode);
                    smsDev.pages = getPagesDataInNode(devEl);
                    listDev.Add(smsDev);
                }
                else if (devType == DEVICE_TYPE.UNKNOW)
                {
                    // currently we reject wrong elements
                    continue;
                }

                
            }
            return listDev;
        }

        public List<PlaylistData> getPlaylists()
        {
            List<PlaylistData> plList = new List<PlaylistData>();

            IEnumerable<XElement> playlists =
                from el in rootElement.Descendants(PLAYLIST_RECORD)
                select el;

            PlaylistData playlistData=null;
            foreach (XElement playlist in playlists)
            {
                playlistData = new PlaylistData();
                playlistData.ID = int.Parse(playlist.Attribute(PLAYLIST_ATTRIBUTE_ID).Value);
                playlistData.Name = playlist.Attribute(PLAYLIST_ATTRIBUTE_NAME).Value;
                playlistData.Info.Delay = (uint)(int.Parse(playlist.Attribute(PLAYLIST_ATTRIBUTE_DELAY).Value));

                playlistData.Info.StartDate = DateTime.Now;
                playlistData.Info.EndDate = DateTime.Now.AddDays(10);

                var programs = (
                    from w in playlist.Elements(PROGRAM_RECORD)
                    select new
                    {
                        //Index = ushort.Parse(w.Element("Index").Value),
                        Width = ushort.Parse(w.Attribute(PROGRAM_IMAGE_ATTRIBUTE_WIDTH).Value),
                        Height = ushort.Parse(w.Attribute(PROGRAM_IMAGE_ATTRIBUTE_HEIGHT).Value),
                        Bpp = ushort.Parse(w.Attribute(PROGRAM_IMAGE_ATTRIBUTE_BPP).Value),
                        Data = w.Value,
                    }
                );
                int i = 0;
                foreach(var program in programs)
                {
                    i++;
                    
                    playlistData.packages.Add(
                        new ProgramData(
                            new DataInfo(
                                (ushort)(i), 
                                program.Width, 
                                program.Height, 
                                program.Bpp),
                            GetBytes(program.Data))
                        );
                }

                playlistData.Info.PackageCount = (ushort)playlistData.packages.Count;
                plList.Add(playlistData);
            }
            return plList;
        }

        /// <summary>
        /// Get playlists from database
        /// </summary>
        /// <returns></returns>
        public List<LedPlaylist> getPlaylistsNew()
        {
            // Create playlist
            List<LedPlaylist> plList = new List<LedPlaylist>();

            // Query Playlist node from data base
            // Select * from Playlists
            IEnumerable<XElement> playlists = from el in rootElement.Descendants(PLAYLIST_RECORD)
                                                select el;

            // Declare ledplaylist object
            LedPlaylist ledplaylist = null;
            foreach (XElement playlist in playlists)
            {
                // Create playlist object
                ledplaylist = new LedPlaylist();
                // set playlist's ID
                ledplaylist.ID = int.Parse(playlist.Attribute(PLAYLIST_ATTRIBUTE_ID).Value);
                // set playlist's name
                ledplaylist.Name = playlist.Attribute(PLAYLIST_ATTRIBUTE_NAME).Value;
                // set playlist's delay
                ledplaylist.Delay = (int.Parse(playlist.Attribute(PLAYLIST_ATTRIBUTE_DELAY).Value));
                // set playlist's start date <HARD_CODE>
                ledplaylist.StartDate = DateTime.Now;
                // set playlist end date <HARD_CODE>
                ledplaylist.EndDate = DateTime.Now.AddDays(10);

                // Query Program node from Playlist
                // Select * from Playlist
                var programs = from p in playlist.Elements(PROGRAM_RECORD)
                                select p;
                // Create program object base on each program node
                int i = 0;// Program Index. Begin from 1.
                foreach (var program in programs)
                {
                    i++;// Increase program index.
                    // get program's id
                    ushort id = ushort.Parse(program.Attribute(PROGRAM_ATTRIBUTE_INDEX).Value);
                    // get program's type
                    string type = program.Attribute(PROGRAM_ATTRIBUTE_TYPE).Value;
                    // convert type string to PROGRAM_TYPE enum
                    PROGRAM_TYPE programType = (PROGRAM_TYPE)Enum.Parse(typeof(PROGRAM_TYPE), type);

                    // check program type
                    if (programType == PROGRAM_TYPE.TEXT) // text program
                    {
                        // TODO: create text program and add to playlist
                        /* Text data example
                         * <Text Font="Arial" Size="10" Color="GREEN" PositionX="0" PositionY="0">
                         *    FPT
                         *    TECHNOLOGY
                         *    SOLUTION
                         *  </Text>
                         * */
                        TextProgram tProgram =
                            (from text in program.Elements(PROGRAM_TEXT_RECORD)
                             select new TextProgram
                             {
                                 // set program's id
                                 ID = id,
                                 // set text font 
                                 FontName = text.Attribute(PROGRAM_TEXT_ATTRIBUTE_FONT).Value,
                                 // set text font size
                                 FontSize = int.Parse(text.Attribute(PROGRAM_TEXT_ATTRIBUTE_SIZE).Value),
                                 // set text color
                                 Color = Color.FromName(text.Attribute(PROGRAM_TEXT_ATTRIBUTE_COLOR).Value),
                                 // set text position
                                 Position = new Point(int.Parse(text.Attribute(PROGRAM_TEXT_ATTRIBUTE_POSX).Value),
                                                        int.Parse(text.Attribute(PROGRAM_TEXT_ATTRIBUTE_POSY).Value)),
                                 Width = int.Parse(text.Attribute(PROGRAM_TEXT_ATTRIBUTE_WIDTH).Value),
                                 Height = int.Parse(text.Attribute(PROGRAM_TEXT_ATTRIBUTE_HEIGHT).Value),
                                 // set text content
                                 Content = text.Value
                             }
                            ).FirstOrDefault();
                        // add program to playlist
                        if (tProgram != null)
                            ledplaylist.Add(tProgram);
                    }
                    else if (programType == PROGRAM_TYPE.IMAGE) // Image program
                    {
                        // TODO: create image program and add to playlist
                        /* Image data example:
                         * <Imgage Width="128" Height="64" Bpp="2">
                         *    <![CDATA[AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADw//8P//8P8P///wAAAAAAAAAAAAAAAAAAAAAAAAAAAPD//w///z/w////AAAAAAAAAAAAAAAAAAAAAAAAAAAA8P//D/////D///8AAAAAAAAAAAAAAAAAAAAAAAAAAADwDwAA/wD/A8A/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPAPAAD/APwDwD8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8A8AAP8A/APAPwAAAAAAAAAAAAAAAAAAAAAAAAAAAADwDwAA/wD8A8A/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPD//wP/APwDwD8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8P//A/8A/wDAPwAAAAAAAAAAAAAAAAAAAAAAAAAAAADw//8D////AMA/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPAPAAD//z8AwD8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8A8AAP//AwDAPwAAAAAAAAAAAAAAAAAAAAAAAAAAAADwDwAA/wAAAMA/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPAPAAD/AAAAwD8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8A8AAP8AAADAPwAAAAAAAAAAAAAAAAAAAAAAAAAAAADwDwAA/wAAAMA/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPAPAAD/AAAAwD8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8A8AAP8AAADAPwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAP//////////////AwAAAAAAAAAAAAAAAAAAAAAAAAAA//////////////8DAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=]]>
                         * </Imgage>
                         * */
                        ImageProgram iProgram =
                            (from image in program.Elements(PROGRAM_IMAGE_RECORD)
                             select new ImageProgram
                             {
                                 // set program's id
                                 ID = id,
                                 // set image's width
                                 Width = ushort.Parse(image.Attribute(PROGRAM_IMAGE_ATTRIBUTE_WIDTH).Value),
                                 // set image's height
                                 Height = ushort.Parse(image.Attribute(PROGRAM_IMAGE_ATTRIBUTE_HEIGHT).Value),
                                 // set image's bpp
                                 BPP = ushort.Parse(image.Attribute(PROGRAM_IMAGE_ATTRIBUTE_BPP).Value),
                                 // set image 64base encoded string data
                                 Base64StringData = image.Value
                             }
                            ).FirstOrDefault();
                        if (iProgram != null)
                            ledplaylist.Add(iProgram);
                    }
                }
                // Add playlist to List of playlist
                plList.Add(ledplaylist);    
            }
            return plList;
        }

        /// <summary>
        /// Get page data from database
        /// </summary>
        /// <returns></returns>
        public List<LETIPage> getPagesData()
        {
            // Create list page
            List<LETIPage> pageLst = new List<LETIPage>();

            // Query PAGE node from data base
            // Select * from PAGES
            IEnumerable<XElement> pages = from el in rootElement.Descendants(PAGE_RECORD)
                                              select el;

            // Declare page object
            LETIPage pageObj;
            foreach (XElement pageEl in pages)
            {
                // Create page object
                pageObj = new LETIPage();
                // set page's ID
                pageObj.Header.ID = ushort.Parse(pageEl.Attribute(PAGE_ATTRIBUTE_ID).Value);
                // set page's item count
                pageObj.Header.ItemCount = (byte.Parse(pageEl.Attribute(PAGE_ATTRIBUTE_ITEM_COUNT).Value));
                // set page's event type
                pageObj.Header.EventType = (EVENT_TYPE)Enum.Parse(typeof(EVENT_TYPE),
                    pageEl.Attribute(PAGE_ATTRIBUTE_EVENT_TYPE).Value);

                // Query POPUP node from PAGE
                // Select * from POPUP
                var popups = from p in pageEl.Elements(POPUP_RECORD)
                                select p;

                // Create popup object base on each popup node
                foreach (var popupEl in popups)
                {
                    
                    try
                    {
                        POPUP_TYPE DataType = (POPUP_TYPE)Enum.Parse(typeof(POPUP_TYPE), popupEl.Attribute(POPUP_ATTRIBUTE_DATA_TYPE).Value);

                        POPUP popup = EnumUtils.getPopupByType(DataType);

                        popup.ID = ushort.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_ID).Value);
                        popup.X = ushort.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_X).Value);
                        popup.Y = ushort.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_Y).Value);
                        popup.Z = byte.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_Z).Value);
                        popup.Width = ushort.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_WIDTH).Value);
                        popup.Height = ushort.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_HEIGHT).Value);
                        //popup.DataType = (POPUP_TYPE)Enum.Parse(typeof(POPUP_TYPE), popupEl.Attribute(POPUP_ATTRIBUTE_DATA_TYPE).Value);
                        popup.StartDate = DATETIME.FromChars((popupEl.Attribute(POPUP_ATTRIBUTE_START).Value).ToCharArray());
                        popup.EndDate = DATETIME.FromChars((popupEl.Attribute(POPUP_ATTRIBUTE_END).Value).ToCharArray());
                        popup.ItemCount = byte.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_ITEM_COUNT).Value);
                        popup.ActiveItem = byte.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_ACTIVE_ITEM).Value);
                        // check if this popup contains playlist data
                        if (popup.DataType == POPUP_TYPE.PLAYLIST)
                        {
                            // Query POPUP node from PAGE
                            // Select * from POPUP
                            var images = from p in popupEl.Elements(IMAGE_RECORD)
                                         select p;
                            // Create image object base on each image node
                            ((PLAYLIST_POPUP)popup).Images = new List<IMAGE>();
                            foreach (var imageEl in images)
                            {
                                IMAGE imgObj = new IMAGE();

                                imgObj.PlayOrder = byte.Parse(imageEl.Attribute(IMAGE_ATTRIBUTE_PLAY_ORDER).Value);
                                imgObj.ImageCode = ushort.Parse(imageEl.Attribute(IMAGE_ATTRIBUTE_IMAGE_CODE).Value);
                                imgObj.Delay = byte.Parse(imageEl.Attribute(IMAGE_ATTRIBUTE_DELAY).Value);
                                imgObj.Effect = (EFFECT)Enum.Parse(typeof(EFFECT), imageEl.Attribute(IMAGE_ATTRIBUTE_EFFECT).Value);

                                ((PLAYLIST_POPUP)popup).Images.Add(imgObj);
                            }
                        }
                        // check if this popup contains table data
                        else if (popup.DataType == POPUP_TYPE.TABLE)
                        {
                            // Query POPUP node from PAGE
                            // Select * from POPUP
                            TABLE table = (from p in popupEl.Elements(TABLE_RECORD)
                                         select new TABLE
                                         {
                                             Rows = byte.Parse(p.Attribute(TABLE_ATTRIBUTE_ROW).Value),
                                             Columns = byte.Parse(p.Attribute(TABLE_ATTRIBUTE_COLUMN).Value),
                                             SplitSymbol = char.Parse(p.Attribute(TABLE_ATTRIBUTE_SYMBOL).Value),
                                             ContentData = p.Value
                                         }).FirstOrDefault();
                            // Create table object base on each image node
                            ((TABLE_POPUP)popup).Table = table;
                        }
                        else if(popup.DataType == POPUP_TYPE.TEXT)
                        {
                            ((TEXT_POPUP)popup).Content = popupEl.Value;
                        }
                        pageObj.AddPopup(popup);
                    }
                    catch (System.Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    
                }
                pageLst.Add(pageObj);
            }

            return pageLst;
        }

        /// <summary>
        /// Get page data from database
        /// </summary>
        /// <returns></returns>
        public List<LETIPage> getPagesDataInNode(XElement baseElement)
        {
            // Create list page
            List<LETIPage> pageLst = new List<LETIPage>();

            // Query PAGE node from data base
            // Select * from PAGES
            IEnumerable<XElement> pages = from el in baseElement.Descendants(PAGE_RECORD)
                                          select el;

            // Declare page object
            LETIPage pageObj;
            foreach (XElement pageEl in pages)
            {
                // Create page object
                pageObj = new LETIPage();
                // set page's ID
                pageObj.Header.ID = ushort.Parse(pageEl.Attribute(PAGE_ATTRIBUTE_ID).Value);
                // set page's item count
                pageObj.Header.ItemCount = (byte.Parse(pageEl.Attribute(PAGE_ATTRIBUTE_ITEM_COUNT).Value));
                // set page's event type
                pageObj.Header.EventType = (EVENT_TYPE)Enum.Parse(typeof(EVENT_TYPE),
                    pageEl.Attribute(PAGE_ATTRIBUTE_EVENT_TYPE).Value);

                // Query POPUP node from PAGE
                // Select * from POPUP
                var popups = from p in pageEl.Elements(POPUP_RECORD)
                             select p;

                // Create popup object base on each popup node
                foreach (var popupEl in popups)
                {
                    try
                    {
                        POPUP_TYPE DataType = (POPUP_TYPE)Enum.Parse(typeof(POPUP_TYPE), popupEl.Attribute(POPUP_ATTRIBUTE_DATA_TYPE).Value);
                        POPUP popup = EnumUtils.getPopupByType(DataType);

                        popup.ID = ushort.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_ID).Value);
                        popup.X = ushort.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_X).Value);
                        popup.Y = ushort.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_Y).Value);
                        popup.Z = byte.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_Z).Value);
                        popup.Width = ushort.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_WIDTH).Value);
                        popup.Height = ushort.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_HEIGHT).Value);
                        // don't need to set type here
                        popup.StartDate = DATETIME.FromChars((popupEl.Attribute(POPUP_ATTRIBUTE_START).Value).ToCharArray());
                        popup.EndDate = DATETIME.FromChars((popupEl.Attribute(POPUP_ATTRIBUTE_END).Value).ToCharArray());
                        popup.ItemCount = byte.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_ITEM_COUNT).Value);
                        popup.ActiveItem = byte.Parse(popupEl.Attribute(POPUP_ATTRIBUTE_ACTIVE_ITEM).Value);
                        // check if this popup contains playlist data
                        if (popup.DataType == POPUP_TYPE.PLAYLIST)
                        {
                            // Query POPUP node from PAGE
                            // Select * from POPUP
                            var images = from p in popupEl.Elements(IMAGE_RECORD)
                                         select p;
                            // Create image object base on each image node
                            ((PLAYLIST_POPUP)popup).Images = new List<IMAGE>();
                            foreach (var imageEl in images)
                            {
                                IMAGE imgObj = new IMAGE();

                                imgObj.PlayOrder = byte.Parse(imageEl.Attribute(IMAGE_ATTRIBUTE_PLAY_ORDER).Value);
                                imgObj.ImageCode = ushort.Parse(imageEl.Attribute(IMAGE_ATTRIBUTE_IMAGE_CODE).Value);
                                imgObj.Delay = byte.Parse(imageEl.Attribute(IMAGE_ATTRIBUTE_DELAY).Value);
                                imgObj.Effect = (EFFECT)Enum.Parse(typeof(EFFECT), imageEl.Attribute(IMAGE_ATTRIBUTE_EFFECT).Value);

                                ((PLAYLIST_POPUP)popup).Images.Add(imgObj);
                            }
                        }
                        // check if this popup contains table data
                        else if (popup.DataType == POPUP_TYPE.TABLE)
                        {
                            // Query POPUP node from PAGE
                            // Select * from POPUP
                            TABLE table = (from p in popupEl.Elements(TABLE_RECORD)
                                           select new TABLE
                                           {
                                               Rows = byte.Parse(p.Attribute(TABLE_ATTRIBUTE_ROW).Value),
                                               Columns = byte.Parse(p.Attribute(TABLE_ATTRIBUTE_COLUMN).Value),
                                               SplitSymbol = char.Parse(p.Attribute(TABLE_ATTRIBUTE_SYMBOL).Value),
                                               ContentData = p.Value
                                           }).FirstOrDefault();
                            // Create table object base on each image node
                            ((TABLE_POPUP)popup).Table = table;
                        }
                        else if (popup.DataType == POPUP_TYPE.TEXT)
                        {
                            ((TEXT_POPUP)popup).Content = popupEl.Value;
                        }
                        pageObj.AddPopup(popup);
                    }
                    catch (System.Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }

                }
                pageLst.Add(pageObj);
            }

            return pageLst;
        }

        public void addPage(string imei, LETIPage page)
        {
            // the verbose way, if you will be removing many elements (though in
            // this case, we're only removing one)
            var devEl = (from XElement e in rootElement.Descendants(DEVICE_RECORD)
                         where e.Attribute(DEVICE_ATTRIBUTE_IMEI).Value == imei
                         select e).Single();
            XElement pageElement = new XElement(PAGE_RECORD,
             new XAttribute(PAGE_ATTRIBUTE_ID, page.Header.ID),
             new XAttribute(PAGE_ATTRIBUTE_ITEM_COUNT, page.Header.ItemCount),
             new XAttribute(PAGE_ATTRIBUTE_EVENT_TYPE, page.Header.EventType));
            if (page.PopUps != null)
            {
                // Create popup object base on each popup node
                foreach (POPUP popup in page.PopUps)
                {
                    XElement popupEl = new XElement(POPUP_RECORD,
                                    new XAttribute(POPUP_ATTRIBUTE_ID, popup.ID),
                                    new XAttribute(POPUP_ATTRIBUTE_X, popup.X),
                                    new XAttribute(POPUP_ATTRIBUTE_Y, popup.Y),
                                    new XAttribute(POPUP_ATTRIBUTE_Z, popup.Z),
                                    new XAttribute(POPUP_ATTRIBUTE_WIDTH, popup.Width),
                                    new XAttribute(POPUP_ATTRIBUTE_HEIGHT, popup.Height),
                                    new XAttribute(POPUP_ATTRIBUTE_DATA_TYPE, popup.DataType),
                                    new XAttribute(POPUP_ATTRIBUTE_ITEM_COUNT, popup.ItemCount),
                                    new XAttribute(POPUP_ATTRIBUTE_ACTIVE_ITEM, popup.ActiveItem),
                                    new XAttribute(POPUP_ATTRIBUTE_START, popup.StartDate),
                                    new XAttribute(POPUP_ATTRIBUTE_END, popup.EndDate)
                                    );
                    if (popup.DataType == POPUP_TYPE.PLAYLIST && ((PLAYLIST_POPUP)popup).Images != null && popup.ItemCount == ((PLAYLIST_POPUP)popup).Images.Count)
                    {
                        foreach (IMAGE img in ((PLAYLIST_POPUP)popup).Images)
                        {
                            popupEl.Add(
                                new XElement(IMAGE_RECORD,
                                    new XAttribute(IMAGE_ATTRIBUTE_PLAY_ORDER, img.PlayOrder),
                                    new XAttribute(IMAGE_ATTRIBUTE_IMAGE_CODE, img.ImageCode),
                                    new XAttribute(IMAGE_ATTRIBUTE_DELAY, img.Delay),
                                    new XAttribute(IMAGE_ATTRIBUTE_EFFECT, img.Effect)
                                )
                            );
                        }
                    }
                    else
                    {
                        Debug.WriteLine("XML database: popup item count and images not match!");
                    }
                    pageElement.Add(popupEl);
                }

                devEl.Element(PAGE_TABLE).Add(pageElement);
                rootElement.Save(DATABASE_NAME);
            }
        }

        public class IMAGEDATA
        {
            public ushort ID{ get; set; }
            public string Path { get; set; }
            public IMAGEDATA(ushort id, string path)
            {
                ID = id;
                Path = path;
            }
        }
        /// <summary>
        /// Get page data from database
        /// </summary>
        /// <returns></returns>
        public List<IMAGEDATA> getImageTable()
        {
            // Create list page
            List<IMAGEDATA> imageLst = new List<IMAGEDATA>();

            // Query Img node from data base
            // Select * from Img
            IEnumerable<XElement> images = from el in rootElement.Descendants(IMAGE_DATA_RECORD)
                                          select el;

            if (images != null)
            foreach (XElement imgEl in images)
            {
                imageLst.Add(new IMAGEDATA(ushort.Parse(imgEl.Attribute(IMAGE_DATA_ATTRIBUTE_ID).Value), 
                                            imgEl.Attribute(IMAGE_DATA_ATTRIBUTE_PATH).Value));
            }

            return imageLst;
        }

        #endregion

        #region | Utils functions |
        /// <summary>
        /// Convert string to byte array
        /// </summary>
        /// <param name="str">Input string</param>
        /// <returns>Byte array</returns>
        static byte[] GetBytes(string str)
        {
            byte[] bytes = new byte[str.Length * sizeof(char)];
            System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
            return bytes;
        }

        /// <summary>
        /// Convert bytearray to string
        /// </summary>
        /// <param name="bytes">Input byte array</param>
        /// <returns>String</returns>
        static string GetString(byte[] bytes)
        {
            char[] chars = new char[bytes.Length / sizeof(char)];
            System.Buffer.BlockCopy(bytes, 0, chars, 0, bytes.Length);
            return new string(chars);
        }
        #endregion
    }
}
